using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace TestSystem.DAL.TestSystem
{
    public class QuizDataAccess : DataAccess
    {
        public static IDataReader GetQuizesBySubjectID(int subjectID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@SubjectID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, subjectID)
                };
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Quiz_GetBySubjectID", paramsArray);
        }
        public static IDataReader GetQuizesBySubjectID(int subjectID, bool selectDeleted, bool selectActive)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@SubjectID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, subjectID),
                    new SqlParameter("@SelectDeleted", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, selectDeleted), 
                    new SqlParameter("@SelectActive", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, selectActive)               
                };
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Quiz_GetBySubjectID", paramsArray);
        }

        public static int InsertQuiz(string title, string description, int subjectID, DateTime addedDate, string addedBy, int passedTimes, 
                                     int failedTimes, int questionsCount, bool useTimer, int timePerQuiz, int scoreToPass, bool active, bool deleted, bool saveUserAnswers, bool allowReAnswer, int maxScores, string levelsPercentage)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@Title", SqlDbType.VarChar, 50, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, title), 
                    new SqlParameter("@Description", SqlDbType.NText, NTEXT, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, description),
                    new SqlParameter("@SubjectID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, subjectID),
                    new SqlParameter("@AddedDate", SqlDbType.DateTime, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, addedDate),
                    new SqlParameter("@AddedBy", SqlDbType.VarChar, 50, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, addedBy),
                    new SqlParameter("@PassedTimes", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, passedTimes),
                    new SqlParameter("@FailedTimes", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, failedTimes),
                    new SqlParameter("@QuestionsCount", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, questionsCount),
                    new SqlParameter("@UseTimer", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, useTimer),
                    new SqlParameter("@TimePerQuiz", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, timePerQuiz),
                    new SqlParameter("@ScoreToPass", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, scoreToPass),
                    new SqlParameter("@Active", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, active),
                    new SqlParameter("@Deleted", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, deleted),
                    new SqlParameter("@SaveUserAnswers", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, saveUserAnswers),
                    new SqlParameter("@AllowReAnswer", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, allowReAnswer),
                    new SqlParameter("@MaxScores", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, maxScores),
                    new SqlParameter("@LevelsPercentage", SqlDbType.VarChar, 50, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, levelsPercentage)
                };
            return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Quiz_Insert", paramsArray));
        }

        public static int InsertQuiz(SqlTransaction tran, string title, string description, int subjectID, DateTime addedDate, string addedBy, int passedTimes,
                                     int failedTimes, int questionsCount, bool useTimer, int timePerQuiz, int scoreToPass, bool active, bool deleted, bool saveUserAnswers, bool allowReAnswer, int maxScores, string levelsPercentage)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@Title", SqlDbType.VarChar, 50, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, title), 
                    new SqlParameter("@Description", SqlDbType.NText, NTEXT, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, description),
                    new SqlParameter("@SubjectID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, subjectID),
                    new SqlParameter("@AddedDate", SqlDbType.DateTime, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, addedDate),
                    new SqlParameter("@AddedBy", SqlDbType.VarChar, 50, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, addedBy),
                    new SqlParameter("@PassedTimes", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, passedTimes),
                    new SqlParameter("@FailedTimes", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, failedTimes),
                    new SqlParameter("@QuestionsCount", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, questionsCount),
                    new SqlParameter("@UseTimer", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, useTimer),
                    new SqlParameter("@TimePerQuiz", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, timePerQuiz),
                    new SqlParameter("@ScoreToPass", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, scoreToPass),
                    new SqlParameter("@Active", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, active),
                    new SqlParameter("@Deleted", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, deleted),
                    new SqlParameter("@SaveUserAnswers", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, saveUserAnswers),
                    new SqlParameter("@AllowReAnswer", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, allowReAnswer),
                    new SqlParameter("@MaxScores", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, maxScores),
                    new SqlParameter("@LevelsPercentage", SqlDbType.VarChar, 50, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, levelsPercentage)
                };
            return Convert.ToInt32(SqlHelper.ExecuteScalar(tran, CommandType.StoredProcedure, "Quiz_Insert", paramsArray));
        }

        public static void DeleteBySubjectID(int subjectID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@SubjectID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, subjectID)
                };
            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Quiz_DeleteBySubjectID", paramsArray);
        }

        public static int GetCountBySubjectID(int subjectID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@SubjectID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, subjectID)
                };
            return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Quiz_GetCountBySubjectID", paramsArray));
        }

        public static int UpdateQuiz(int quizID, string title, string description, int subjectID, DateTime addedDate, string addedBy, int passedTimes,
                                     int failedTimes, int questionsCount, bool useTimer, int timePerQuiz, int scoreToPass, bool active, bool deleted, bool saveUserAnswers, bool allowReAnswer, int maxScores, string levelsPercentage)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@QuizID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, quizID), 
                    new SqlParameter("@Title", SqlDbType.VarChar, 50, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, title), 
                    new SqlParameter("@Description", SqlDbType.NText, NTEXT, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, description),
                    new SqlParameter("@SubjectID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, subjectID),
                    new SqlParameter("@AddedDate", SqlDbType.DateTime, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, addedDate),
                    new SqlParameter("@AddedBy", SqlDbType.VarChar, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, addedBy),
                    new SqlParameter("@PassedTimes", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, passedTimes),
                    new SqlParameter("@FailedTimes", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, failedTimes),
                    new SqlParameter("@QuestionsCount", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, questionsCount),
                    new SqlParameter("@UseTimer", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, useTimer),
                    new SqlParameter("@TimePerQuiz", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, timePerQuiz),
                    new SqlParameter("@ScoreToPass", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, scoreToPass),
                    new SqlParameter("@Active", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, active),
                    new SqlParameter("@Deleted", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, deleted),
                    new SqlParameter("@SaveUserAnswers", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, saveUserAnswers),
                    new SqlParameter("@AllowReAnswer", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, allowReAnswer),
                    new SqlParameter("@MaxScores", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, maxScores),
                    new SqlParameter("@LevelsPercentage", SqlDbType.VarChar, 50, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, levelsPercentage)
                };
            return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Quiz_Update", paramsArray));
        }

        public static void Delete(int quizID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@QuizID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, quizID)
                };
            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Quiz_Delete", paramsArray);
        }

        public static IDataReader GetAll()
        {
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Quiz_GetAll");
        }

        public static IDataReader GetAll(bool selectDeleted, bool selectActive)
        {
            SqlParameter [] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@SelectDeleted", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, selectDeleted),     
                    new SqlParameter("@SelectActive", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, selectActive)     
                };
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Quiz_GetAll", paramsArray);
        }

        public static IDataReader GetByQuizID(int quizID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@QuizID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, quizID)     
                };
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Quiz_GetByQuizID", paramsArray);
        }

        public static int GetCount()
        {
            return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Quiz_GetCount"));
        }
    }
}
